alter table Business
add SearchIndex varchar(max) null
go
create FUNCTION fnBusinessCategory_SearchIndex
(
  @BusinessId int
)
RETURNS varchar(max)
AS  
BEGIN
	declare @Result varchar(max)
	set @Result = '';
		
	WITH CategoryLevelList
	AS
	(
		SELECT Category.CategoryId, Category.ParentId, Category.CategoryName
		FROM BusinessCategory (nolock) left join Category (nolock) on BusinessCategory.CategoryId = Category.CategoryId
		WHERE (BusinessCategory.BusinessId = @BusinessId)
			and (Category.IsRoot = 0)
		
		UNION ALL

		SELECT c.CategoryId, c.ParentId, c.CategoryName
		FROM Category (nolock) AS c INNER JOIN CategoryLevelList AS cll ON c.CategoryId = cll.ParentId
		WHERE (c.IsRoot = 0)
	)
	
	select @Result = @Result + ' ' + CategoryName
	FROM (select distinct CategoryName from CategoryLevelList) as vw
		
	RETURN(rtrim(@Result))
END
go
-- update current business if needed
/*update Business
set SearchIndex = dbo.fnBusinessCategory_SearchIndex(BusinessId)
go*/
create PROCEDURE spBusiness_Set_SearchIndex
(
	@BusinessId int
)
AS
BEGIN
	SET NOCOUNT ON
	
	update Business
	set SearchIndex = dbo.fnBusinessCategory_SearchIndex(BusinessId)
	where BusinessId = @BusinessId
END
go	
ALTER PROCEDURE [dbo].[spAPI_Business_Register]
	@login varchar(50)
	,@password varchar(50)
	,@email varchar(50)
	,@businessName varchar(100)
	,@web varchar(100)
	,@businessPhone varchar(50)
	,@cellPhone varchar(50)
	,@tollFreePhone varchar(50)
	,@fax varchar(50)
	,@streetAddress varchar(100)
	,@streetAddress2 varchar(100)
	,@city varchar(50)
	,@state varchar(2)
	,@zip varchar(5)
	,@description varchar(MAX)
	,@referencedBy varchar(100)
	,@sunFrom datetime
	,@sunTo datetime
	,@sunClosed bit
	,@monFrom datetime
	,@monTo datetime
	,@monClosed bit
	,@tueFrom datetime
	,@tueTo datetime
	,@tueClosed bit
	,@wedFrom datetime
	,@wedTo datetime
	,@wedClosed bit
	,@thuFrom datetime
	,@thuTo datetime
	,@thuClosed bit
	,@friFrom datetime
	,@friTo datetime
	,@friClosed bit
	,@satFrom datetime
	,@satTo datetime
	,@satClosed bit
	,@categories varchar(max)
	,@newCategory1 varchar(250)
	,@newCategory2 varchar(250)
	,@newCategory3 varchar(250)
	,@latitude float
	,@longitude float
	,@gmt int = 0
	,@creditCardNo varchar(20)
	,@creditCardExpDate datetime
	,@creditCardName varchar(50)
	,@creditCardPin varchar(10)
	,@useBusinessAddress bit
	,@billingStreetAddress varchar(100)
	,@billingStreetAddress2 varchar(100)
	,@billingCity varchar(50)
	,@billingState varchar(2)
	,@billingZip varchar(5)
	
	,@PictureContentType VARCHAR(50) = ''
	,@Picture varbinary(max) = null	
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	DECLARE @IdResult INT
	SET @IdResult = 1
	
	-- delete business with same login or email and not registered
	exec spBusiness_NotRegistered_Delete @Login = @login, @Email = @email
	
	IF ISNULL(@referencedBy, '') <> ''
		IF NOT EXISTS(SELECT 1 FROM UserBusinessLogins WHERE LoginName = @referencedBy)
		BEGIN
			SET @IdResult = 0
			SELECT @IdResult AS IdResult
			RETURN
		END

	IF ISNULL(@zip, '') <> ''
		IF NOT EXISTS(SELECT 1 FROM ZatifyGEO..ZipCode WHERE ZipCode = @zip)
		BEGIN
			SET @IdResult = -3
			SELECT @IdResult AS IdResult
			RETURN
		END

	IF ISNULL(@billingZip, '') <> ''
		IF NOT EXISTS(SELECT 1 FROM ZatifyGEO..ZipCode WHERE ZipCode = @billingZip)
		BEGIN
			SET @IdResult = -4
			SELECT @IdResult AS IdResult
			RETURN
		END
		
	IF ISNULL(@Email, '') <> ''
		IF EXISTS(SELECT 1 FROM [User] (NOLOCK) WHERE Email = @Email) OR
			EXISTS(SELECT 1 FROM Business (NOLOCK) WHERE Email = @Email)
		BEGIN
			SET @IdResult = -2
			SELECT @IdResult AS IdResult
			RETURN
		END
	
	BEGIN TRANSACTION
	
		INSERT INTO UserBusinessLogins(LoginName, LoginType)
		VALUES(@login, 'B')
		if @@error <> 0 return
	
		INSERT INTO [Business]
			([Login]
			,[Email]
			,[Password]
			,[BusinessName]
			,[Web]
			,[BusinessPhone]
			,[CellPhone]
			,[TollFreePhone]
			,[Fax]
			,[StreetAddress1]
			,[StreetAddress2]
			,[IsActive]
			,[Deleted]
			,[City]
			,[StateCode]
			,[ZipCode]
			,[Description]
			,[CreditCardNo]
			,[CreditCardExpDate]
			,[CreditCardName]
			,[CreditCardPin]
			,[BillingAddress1]
			,[BillingAddress2]
			,[BillingCity]
			,[BillingStateCode]
			,[BillingZipCode]
			,[BillingUse]
			,[ReferencedBy]
			,[Latitude]
			,[Longitude]
			,Gmt
			,PictureContentType
			,Picture)
		 VALUES
			(@Login
			,@Email
			,@Password
			,@BusinessName
			,@Web
			,@BusinessPhone
			,@CellPhone
			,@TollFreePhone
			,@Fax
			,@StreetAddress
			,@StreetAddress2
			,1
			,0
			,@City
			,@State
			,@Zip
			,@Description
			,@CreditCardNo
			,@CreditCardExpDate
			,@CreditCardName
			,@CreditCardPin
			,@BillingStreetAddress
			,@BillingStreetAddress2
			,@BillingCity
			,@BillingState
			,@BillingZip
			,@useBusinessAddress
			,@ReferencedBy
			,@Latitude
			,@Longitude
			,@gmt
			,@PictureContentType
			,@Picture)
		
		declare @businessId int = SCOPE_IDENTITY()
		exec spAccount_Add @BusinessId = @businessId
		
		INSERT INTO BusinessHours(BusinessId, [WeekDay], FromTime, ToTime, Closed)
		values
			(@BusinessId, 'Sun', @SunFrom, @SunTo, @SunClosed)
			,(@BusinessId, 'Mon', @MonFrom, @MonTo, @MonClosed)
			,(@BusinessId, 'Tue', @TueFrom, @TueTo, @TueClosed)
			,(@BusinessId, 'Wed', @WedFrom, @WedTo, @WedClosed)
			,(@BusinessId, 'Thu', @ThuFrom, @ThuTo, @ThuClosed)
			,(@BusinessId, 'Fri', @FriFrom, @FriTo, @FriClosed)
			,(@BusinessId, 'Sat', @SatFrom, @SatTo, @SatClosed)
    
    
		INSERT INTO BusinessCategory(BusinessId, CategoryId)
		SELECT @BusinessId, CONVERT(INT, Items.Item)
		FROM dbo.fnAPI_String_Split(@Categories, ',') AS Items
		
		exec spBusiness_Set_SearchIndex @BusinessId = @businessId
		
		IF (@newCategory1 IS NOT NULL)
			IF (@newCategory1 <> '')
				INSERT INTO CategoryRequest
				(BusinessId, CategoryName)
				VALUES (@BusinessId, @newCategory1)
		IF (@newCategory2 IS NOT NULL)
			IF (@newCategory2 <> '')
				INSERT INTO CategoryRequest
				(BusinessId, CategoryName)
				VALUES (@BusinessId, @newCategory2)
		IF (@newCategory3 IS NOT NULL)
			IF (@newCategory3 <> '')
				INSERT INTO CategoryRequest
				(BusinessId, CategoryName)
				VALUES (@BusinessId, @newCategory3)
						
		declare @SecurityToken uniqueidentifier = NEWID()
					
		INSERT INTO UserToken(SecurityToken, LoginName, ExpirationDate)
		VALUES(@SecurityToken, @Login, '99991231')
		
		SELECT @IdResult AS IdResult, @SecurityToken AS SecurityToken

	COMMIT TRANSACTION
END
go
ALTER PROCEDURE [dbo].[spAPI_Business_RegisterStep2]
	@Token VARCHAR(255)
	,@description varchar(MAX)
	,@referencedBy varchar(100)
	,@sunFrom datetime
	,@sunTo datetime
	,@sunClosed bit
	,@monFrom datetime
	,@monTo datetime
	,@monClosed bit
	,@tueFrom datetime
	,@tueTo datetime
	,@tueClosed bit
	,@wedFrom datetime
	,@wedTo datetime
	,@wedClosed bit
	,@thuFrom datetime
	,@thuTo datetime
	,@thuClosed bit
	,@friFrom datetime
	,@friTo datetime
	,@friClosed bit
	,@satFrom datetime
	,@satTo datetime
	,@satClosed bit
	,@categories varchar(max)
	,@newCategory1 varchar(250)
	,@newCategory2 varchar(250)
	,@newCategory3 varchar(250)
	,@creditCardNo varchar(20)
	,@creditCardExpDate datetime
	,@creditCardName varchar(50)
	,@creditCardPin varchar(10)
	,@useBusinessAddress bit
	,@billingStreetAddress varchar(100)
	,@billingStreetAddress2 varchar(100)
	,@billingCity varchar(50)
	,@billingState varchar(2)
	,@billingZip varchar(5)
	
	,@PictureContentType VARCHAR(50) = ''
	,@Picture varbinary(max) = null	
	
	,@latitude float = null
	,@longitude float = null
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @IgnoreActiveState = 1, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	if @IdResult = 0
	begin
		SELECT @IdResult AS IdResult
		return
	end
	
	SET @IdResult = 1
	
	IF ISNULL(@referencedBy, '') <> ''
		IF NOT EXISTS(SELECT 1 FROM UserBusinessLogins (nolock) WHERE LoginName = @referencedBy)
		BEGIN
			SET @IdResult = 0
			SELECT @IdResult AS IdResult
			RETURN
		END
	
	IF ISNULL(@billingZip, '') <> ''
		IF NOT EXISTS(SELECT 1 FROM ZatifyGEO..ZipCode (nolock) WHERE ZipCode = @billingZip)
		BEGIN
			SET @IdResult = -4
			SELECT @IdResult AS IdResult
			RETURN
		END
	
	BEGIN TRANSACTION
		
		update [Business]
		set
			[Description] = @Description
			,[CreditCardNo] = @CreditCardNo
			,[CreditCardExpDate] = @CreditCardExpDate
			,[CreditCardName] = @CreditCardName
			,[CreditCardPin] = @CreditCardPin
			,[BillingAddress1] = @BillingStreetAddress
			,[BillingAddress2] = @BillingStreetAddress2
			,[BillingCity] = @BillingCity
			,[BillingStateCode] = @BillingState
			,[BillingZipCode] = @BillingZip
			,[BillingUse] = @useBusinessAddress
			,[ReferencedBy] = @ReferencedBy
			,PictureContentType = @PictureContentType
			,Picture = @Picture
			,latitude = @latitude
			,longitude = @longitude
			,IsActive = 1
			,IsRegistered = 1
		where BusinessId = @businessId	
		
		
		delete from BusinessHours where BusinessId = @BusinessId
		
		INSERT INTO BusinessHours(BusinessId, [WeekDay], FromTime, ToTime, Closed)
		values
			(@BusinessId, 'Sun', @SunFrom, @SunTo, @SunClosed)
			,(@BusinessId, 'Mon', @MonFrom, @MonTo, @MonClosed)
			,(@BusinessId, 'Tue', @TueFrom, @TueTo, @TueClosed)
			,(@BusinessId, 'Wed', @WedFrom, @WedTo, @WedClosed)
			,(@BusinessId, 'Thu', @ThuFrom, @ThuTo, @ThuClosed)
			,(@BusinessId, 'Fri', @FriFrom, @FriTo, @FriClosed)
			,(@BusinessId, 'Sat', @SatFrom, @SatTo, @SatClosed)
    
    
		delete from BusinessCategory where BusinessId = @BusinessId
    
		INSERT INTO BusinessCategory(BusinessId, CategoryId)
		SELECT @BusinessId, CONVERT(INT, Items.Item)
		FROM dbo.fnAPI_String_Split(@Categories, ',') AS Items
		
		exec spBusiness_Set_SearchIndex @BusinessId = @businessId		
		
		IF (@newCategory1 IS NOT NULL) and (@newCategory1 <> '')
			INSERT INTO CategoryRequest
			(BusinessId, CategoryName)
			VALUES (@BusinessId, @newCategory1)
		IF (@newCategory2 IS NOT NULL) and (@newCategory2 <> '')
			INSERT INTO CategoryRequest
			(BusinessId, CategoryName)
			VALUES (@BusinessId, @newCategory2)
		IF (@newCategory3 IS NOT NULL) and (@newCategory3 <> '')
			INSERT INTO CategoryRequest
			(BusinessId, CategoryName)
			VALUES (@BusinessId, @newCategory3)
						
		SELECT @IdResult AS IdResult

	COMMIT TRANSACTION
END
go
ALTER PROCEDURE [dbo].[spAPI_Business_Update]
	@Token VARCHAR(255)
	,@password varchar(50)
	,@email varchar(50)
	,@businessName varchar(100)
	,@web varchar(100)
	,@businessPhone varchar(50)
	,@cellPhone varchar(50)
	,@tollFreePhone varchar(50)
	,@fax varchar(50)
	,@streetAddress varchar(100)
	,@streetAddress2 varchar(100)
	,@city varchar(50)
	,@state varchar(2)
	,@zip varchar(50)
	,@description varchar(MAX)
	,@referencedBy varchar(100)
	,@sunFrom datetime
	,@sunTo datetime
	,@sunClosed bit
	,@monFrom datetime
	,@monTo datetime
	,@monClosed bit
	,@tueFrom datetime
	,@tueTo datetime
	,@tueClosed bit
	,@wedFrom datetime
	,@wedTo datetime
	,@wedClosed bit
	,@thuFrom datetime
	,@thuTo datetime
	,@thuClosed bit
	,@friFrom datetime
	,@friTo datetime
	,@friClosed bit
	,@satFrom datetime
	,@satTo datetime
	,@satClosed bit
	,@categories varchar(max)
	,@newCategory1 varchar(250)
	,@newCategory2 varchar(250)
	,@newCategory3 varchar(250)
	,@latitude float
	,@longitude float
	,@gmt int = 0
	,@creditCardNo varchar(20)
	,@creditCardExpDate datetime
	,@creditCardName varchar(50)
	,@creditCardPin varchar(10)
	,@useBusinessAddress bit
	,@billingStreetAddress varchar(100)
	,@billingStreetAddress2 varchar(100)
	,@billingCity varchar(50)
	,@billingState varchar(2)
	,@billingZip varchar(5)
	
	,@PictureContentType VARCHAR(50) = ''
	,@Picture varbinary(max) = null	
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT

	IF @IdResult = 1	
	begin
		IF ISNULL(@zip, '') <> ''
			IF NOT EXISTS(SELECT 1 FROM ZatifyGEO..ZipCode WHERE ZipCode = @zip)
			BEGIN
				SET @IdResult = -3
				SELECT @IdResult AS IdResult
				RETURN
			END

		IF ISNULL(@billingZip, '') <> ''
			IF NOT EXISTS(SELECT 1 FROM ZatifyGEO..ZipCode WHERE ZipCode = @billingZip)
			BEGIN
				SET @IdResult = -4
				SELECT @IdResult AS IdResult
				RETURN
			END
	
		declare @oldEmail varchar(100) = (select email from Business (nolock) where BusinessId = @BusinessId)
		IF (ISNULL(@Email, '') <> '') and @email <> @oldEmail
		begin
			IF EXISTS(SELECT 1 FROM [User] (NOLOCK) WHERE Email = @Email) OR
				EXISTS(SELECT 1 FROM Business (NOLOCK) WHERE Email = @Email)
			BEGIN
				SET @IdResult = -2
				SELECT @IdResult AS IdResult
				RETURN
			END
		end
	
		BEGIN TRANSACTION		
			update [Business]
			set
				[Email] = @Email
				,[Password] = @Password
				,[BusinessName] = @BusinessName
				,[Web] = @Web
				,[BusinessPhone] = @BusinessPhone
				,[CellPhone] = @CellPhone
				,[TollFreePhone] = @TollFreePhone
				,[Fax] = @Fax
				,[StreetAddress1] = @StreetAddress
				,[StreetAddress2] = @StreetAddress2
				,[City] = @City
				,[StateCode] = @State
				,[ZipCode] = @Zip
				,[Description] = @Description
				,[CreditCardNo] = @CreditCardNo
				,[CreditCardExpDate] = @CreditCardExpDate
				,[CreditCardName] = @CreditCardName
				,[CreditCardPin] = @CreditCardPin
				,[BillingAddress1] = @BillingStreetAddress
				,[BillingAddress2] = @BillingStreetAddress2
				,[BillingCity] = @BillingCity
				,[BillingStateCode] = @BillingState
				,[BillingZipCode] = @BillingZip
				,[BillingUse] = @useBusinessAddress
				,[ReferencedBy] = @ReferencedBy
				,[Latitude] = @Latitude
				,[Longitude] = @Longitude
				,Gmt = @gmt
				,PictureContentType = @PictureContentType
				,Picture = @Picture
			where businessId = @businessId
			
			
			delete from BusinessHours where businessId = @businessId
			
			INSERT INTO BusinessHours(BusinessId, [WeekDay], FromTime, ToTime, Closed)
			values
				(@BusinessId, 'Sun', @SunFrom, @SunTo, @SunClosed)
				,(@BusinessId, 'Mon', @MonFrom, @MonTo, @MonClosed)
				,(@BusinessId, 'Tue', @TueFrom, @TueTo, @TueClosed)
				,(@BusinessId, 'Wed', @WedFrom, @WedTo, @WedClosed)
				,(@BusinessId, 'Thu', @ThuFrom, @ThuTo, @ThuClosed)
				,(@BusinessId, 'Fri', @FriFrom, @FriTo, @FriClosed)
				,(@BusinessId, 'Sat', @SatFrom, @SatTo, @SatClosed)
	    
	    
			delete from BusinessCategory where businessId = @businessId
			
			INSERT INTO BusinessCategory(BusinessId, CategoryId)
			SELECT @BusinessId, CONVERT(INT, Items.Item)
			FROM dbo.fnAPI_String_Split(@Categories, ',') AS Items
			
			exec spBusiness_Set_SearchIndex @BusinessId = @businessId
			
			IF isnull(@newCategory1, '') <> ''
				INSERT INTO CategoryRequest
				(BusinessId, CategoryName)
				VALUES (@BusinessId, @newCategory1)
			IF isnull(@newCategory2, '') <> ''
				INSERT INTO CategoryRequest
				(BusinessId, CategoryName)
				VALUES (@BusinessId, @newCategory2)
			IF isnull(@newCategory3, '') <> ''
				INSERT INTO CategoryRequest
				(BusinessId, CategoryName)
				VALUES (@BusinessId, @newCategory3)
							
		COMMIT TRANSACTION
	end
		
	SELECT @IdResult AS IdResult
END
go
ALTER PROCEDURE [dbo].[spBusiness_DeleteCategories]
(
	@businessId int,
	@CategoriesId VARCHAR(MAX)
)
AS

BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON

	begin transaction
		delete from BusinessCategory
		where BusinessId = @businessId
			and CategoryId in (SELECT CONVERT(INT, Items.Item) FROM dbo.fnAPI_String_Split(@CategoriesId, ',') AS Items)
		
		exec spBusiness_Set_SearchIndex @BusinessId = @businessId
	commit transaction
END
go
ALTER PROCEDURE [dbo].[spBusiness_Update]
(
	@BusinessId INT = 0,
	
	@Login VARCHAR(50),
	@Email VARCHAR(50),
	@Password VARCHAR(50),
	@BusinessName VARCHAR(100),
	@Web VARCHAR(100),
	@BusinessPhone VARCHAR(50),
	@CellPhone VARCHAR(50),
	@TollFreePhone VARCHAR(50),
	@Fax VARCHAR(50),
	@StreetAddress1 VARCHAR(100),
	@StreetAddress2 VARCHAR(100),
	@City VARCHAR(50),
	@StateCode VARCHAR(2),
	@ZipCode VARCHAR(5),
	@Description VARCHAR(MAX),
	
	@CreditCardNo VARCHAR(20),
	@CreditCardExpDate DATETIME,
	@CreditCardName VARCHAR(50),
	@CreditCardPin VARCHAR(10),
	
	@BillingAddress1 VARCHAR(100),
	@BillingAddress2 VARCHAR(100),
	@BillingCity VARCHAR(50),
	@BillingStateCode VARCHAR(2),
	@BillingZipCode VARCHAR(5),
	@BillingUse BIT,
	
	@SunFrom DATETIME,
	@SunTo DATETIME,
	@SunClosed BIT,
	@MonFrom DATETIME,
	@MonTo DATETIME,
	@MonClosed BIT,
	@TueFrom DATETIME,
	@TueTo DATETIME,
	@TueClosed BIT,
	@WedFrom DATETIME,
	@WedTo DATETIME,
	@WedClosed BIT,
	@ThuFrom DATETIME,
	@ThuTo DATETIME,
	@ThuClosed BIT,
	@FriFrom DATETIME,
	@FriTo DATETIME,
	@FriClosed BIT,
	@SatFrom DATETIME,
	@SatTo DATETIME,
	@SatClosed BIT,
	
	@Categories VARCHAR(MAX),
	
	@Picture varbinary(max) = null,
	@PictureContentType varchar(50) = '',
	
	@Latitude float = null,
	@Longitude float = null
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	BEGIN TRANSACTION
		IF (@BusinessId = 0)
		BEGIN
			INSERT INTO UserBusinessLogins
			(LoginName, LoginType)
			VALUES(@Login, 'B')
			
			INSERT INTO Business
			([Login], Email, [Password], BusinessName, Web, BusinessPhone, CellPhone, TollFreePhone, Fax, StreetAddress1, StreetAddress2, 
			IsActive, Deleted, City, StateCode, ZipCode, [Description], 
			CreditCardNo, CreditCardExpDate, CreditCardName, CreditCardPin, 
			BillingAddress1, BillingAddress2, BillingCity, BillingStateCode, BillingZipCode, BillingUse,
			Picture, PictureContentType, Latitude, Longitude)
			VALUES (@Login, @Email, @Password, @BusinessName, @Web, @BusinessPhone, @CellPhone, @TollFreePhone, @Fax, @StreetAddress1, @StreetAddress2, 
				1, 0, @City, @StateCode, @ZipCode, @Description, 
				@CreditCardNo, @CreditCardExpDate, @CreditCardName, @CreditCardPin, 
				@BillingAddress1, @BillingAddress2, @BillingCity, @BillingStateCode, @BillingZipCode, @BillingUse,
				@Picture, @PictureContentType, @Latitude, @Longitude)
				
			SET @BusinessId = SCOPE_IDENTITY()
			exec spAccount_Add @BusinessId = @BusinessId
			
			INSERT INTO BusinessHours
			(BusinessId, [WeekDay], FromTime, ToTime, Closed)
			SELECT @BusinessId, 'Sun', @SunFrom, @SunTo, @SunClosed
			UNION
			SELECT @BusinessId, 'Mon', @MonFrom, @MonTo, @MonClosed
			UNION
			SELECT @BusinessId, 'Tue', @TueFrom, @TueTo, @TueClosed
			UNION
			SELECT @BusinessId, 'Wed', @WedFrom, @WedTo, @WedClosed
			UNION
			SELECT @BusinessId, 'Thu', @ThuFrom, @ThuTo, @ThuClosed
			UNION
			SELECT @BusinessId, 'Fri', @FriFrom, @FriTo, @FriClosed
			UNION
			SELECT @BusinessId, 'Sat', @SatFrom, @SatTo, @SatClosed
		END
		ELSE BEGIN
			UPDATE Business
			SET [Login] = @Login, Email = @Email, [Password] = @Password, BusinessName = @BusinessName, Web = @Web, 
				BusinessPhone = @BusinessPhone, CellPhone = @CellPhone, TollFreePhone = @TollFreePhone, Fax = @Fax, 
				StreetAddress1 = @StreetAddress1, StreetAddress2 = @StreetAddress2, 
				City = @City, StateCode = @StateCode, ZipCode = @ZipCode, 
				[Description] = @Description, 
				CreditCardNo = @CreditCardNo, CreditCardExpDate = @CreditCardExpDate, CreditCardName = @CreditCardName, CreditCardPin = @CreditCardPin, 
				BillingAddress1 = @BillingAddress1, BillingAddress2 = @BillingAddress2, BillingCity = @BillingCity, BillingStateCode = @BillingStateCode, BillingZipCode = @BillingZipCode, BillingUse = @BillingUse
				,Picture = @Picture, PictureContentType = @PictureContentType,
				Latitude = isnull(@Latitude, Latitude), Longitude = isnull(@Longitude, Longitude)
			WHERE (BusinessId = @BusinessId)
			
			UPDATE BusinessHours
			SET FromTime = vw.FromTime, ToTime = vw.ToTime, Closed = vw.Closed
			FROM (SELECT @BusinessId AS BusinessId, 'Sun' AS WD, @SunFrom AS FromTime, @SunTo AS ToTime, @SunClosed AS Closed
				UNION
				SELECT @BusinessId, 'Mon', @MonFrom, @MonTo, @MonClosed
				UNION
				SELECT @BusinessId, 'Tue', @TueFrom, @TueTo, @TueClosed
				UNION
				SELECT @BusinessId, 'Wed', @WedFrom, @WedTo, @WedClosed
				UNION
				SELECT @BusinessId, 'Thu', @ThuFrom, @ThuTo, @ThuClosed
				UNION
				SELECT @BusinessId, 'Fri', @FriFrom, @FriTo, @FriClosed
				UNION
				SELECT @BusinessId, 'Sat', @SatFrom, @SatTo, @SatClosed) AS vw
			WHERE (BusinessHours.BusinessId = vw.BusinessId)
				AND (BusinessHours.[WeekDay] = vw.WD)
		END	
				
		DELETE 
		FROM BusinessCategory
		WHERE (BusinessId = @BusinessId)
			
		INSERT INTO BusinessCategory
		(BusinessId, CategoryId)
		SELECT @BusinessId, CONVERT(INT, Items.Item)
		FROM dbo.fnAPI_String_Split(@Categories, ',') AS Items
		
		exec spBusiness_Set_SearchIndex @BusinessId = @BusinessId
	
	COMMIT TRANSACTION
END
go
ALTER PROCEDURE [dbo].[spAPI_Business_Nearby]
(
	@Token VARCHAR(255),
	@Lat FLOAT,
	@Long FLOAT,
	@returnAsDataset bit = 0,
	@searchString varchar(100) = ''
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	if isnull(@searchString, '') = ''
		set @searchString = '""'

	
	DECLARE @IdResult INT, @UserId INT
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
	begin
		if @returnAsDataset = 0
		begin
			SELECT @Result = (SELECT top(50) Business.BusinessId, Business.BusinessName,
							STR(dbo.fnCalculateDistance(@Lat, @Long, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
									vwBusinessRating.RatingText AS Rating,
									vwBusinessRating.CountReviews AS Reviews,
									15 AS PriceRange,
									Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
									Business.[Description], Business.BusinessPhone, Business.CellPhone, 
									Business.TollFreePhone, Business.Web,
									Business.Latitude, Business.Longitude,
									(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name, Category.CategoryDescription
										FROM BusinessCategory LEFT JOIN Category ON BusinessCategory.CategoryId = Category.CategoryId
										WHERE BusinessCategory.BusinessId = Business.BusinessId
										FOR XML AUTO, TYPE) AS Categories,
									(SELECT [WeekDay] AS Name, 
										dbo.fnAPI_Format_Time(FromTime) AS Start, 
										dbo.fnAPI_Format_Time(ToTime) AS [End], 
										Closed
									FROM BusinessHours AS [Day]
									WHERE [Day].BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS BusinessHours,
									ISNULL(vwLike.Likes, 0) AS Likes, 
									ISNULL(vwLike.Dislikes, 0) AS Dislikes, 
									ISNULL(vwLike.IsFeedbackLeft, 0) AS IsFeedbackLeft,
									case when uf.BusinessId is null then 0 else 1 end as IsFavorite,
									vwCheckin.LastCheckinDate
								FROM Business LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
											LEFT JOIN (SELECT BusinessId,
															SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
															SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
															CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
														FROM BusinessLike
														GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId			
											LEFT JOIN BlockBusiness AS bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
											LEFT JOIN UserFavorites AS uf ON uf.BusinessId = Business.BusinessId AND uf.UserId = @UserId
											LEFT JOIN (SELECT Promotion.BusinessId, MAX(PromotionCheckin.DateCheckin) AS LastCheckinDate 
														FROM PromotionCheckin LEFT JOIN Promotion ON PromotionCheckin.PromotionId = Promotion.PromotionId
														WHERE PromotionCheckin.UserId = @UserId
														GROUP BY Promotion.BusinessId) AS vwCheckin ON Business.BusinessId = vwCheckin.BusinessId
								WHERE (Business.IsActive = 1)
									AND (bb.UserId IS NULL)
									and
									(
										@searchString = '""'
										or contains(Business.*, @searchString)
									)									
								ORDER BY Distance --ZH 3/24/14
								FOR XML PATH ('Business'), ROOT('Businesses'))
		end
		else
		begin
			SELECT
				Business.BusinessId,
				Business.[Login],
				Business.Email,
				Business.[Password],
				Business.BusinessName, 
				Business.Web,
				Business.BusinessPhone,
				Business.CellPhone,
				Business.TollFreePhone,
				Business.Fax,
				Business.StreetAddress1,
				Business.StreetAddress2, 
				Business.IsActive,
				Business.Deleted, 
				Business.City,
				Business.StateCode,
				Business.ZipCode,
				Business.[Description], 
				Business.CreditCardNo,
				Business.CreditCardExpDate,
				Business.CreditCardName,
				Business.CreditCardPin, 
				Business.BillingAddress1,
				Business.BillingAddress2,
				Business.BillingCity,
				Business.BillingStateCode,
				Business.BillingZipCode,
				Business.BillingUse, 
				Business.Latitude,
				Business.Longitude, 
				CONVERT(VARBINARY(MAX), NULL) AS Picture,
				Business.PictureContentType, 
				Business.ReferencedBy,
				Business.DateAdded,
				Open24_7 = dbo.fnIsBusinessOpened24_7(Business.BusinessId),
				isnull(vwLike.Likes, 0) as Likes,
				isnull(vwLike.Dislikes, 0) as Dislikes,
				vwBusinessRating.Rating,
				vwBusinessRating.CountReviews AS ReviewsCount,
				Business.Gmt
			FROM Business LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
						LEFT JOIN (SELECT BusinessId,
										SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
										SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
										CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
									FROM BusinessLike
									GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId			
						LEFT JOIN BlockBusiness AS bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
						LEFT JOIN UserFavorites AS uf ON uf.BusinessId = Business.BusinessId AND uf.UserId = @UserId
						LEFT JOIN (SELECT Promotion.BusinessId, MAX(PromotionCheckin.DateCheckin) AS LastCheckinDate 
									FROM PromotionCheckin LEFT JOIN Promotion ON PromotionCheckin.PromotionId = Promotion.PromotionId
									WHERE PromotionCheckin.UserId = @UserId
									GROUP BY Promotion.BusinessId) AS vwCheckin ON Business.BusinessId = vwCheckin.BusinessId											
			WHERE (Business.IsActive = 1)
				AND (bb.UserId IS NULL)	
				and
				(
					@searchString = '""'
					or contains(Business.*, @searchString)
				)
						
			return
		end		
	end

	if @returnAsDataset = 0
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
go
ALTER PROCEDURE [dbo].[spAPI_Business_Nearby_GetByZip]
(
	@Token VARCHAR(255),
	@zipCode VARCHAR(255),
	@searchString varchar(100) = ''
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	if isnull(@searchString, '') = ''
		set @searchString = '""'
	
	DECLARE @IdResult INT, @UserId INT
	DECLARE @Result XML	
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
	begin
		declare @city varchar(100)
		set @city = (select City from ZatifyGEO..ZipCode (nolock) where ZipCode = @zipCode)
		
		SELECT
			Business.BusinessId,
			Business.[Login],
			Business.Email,
			Business.[Password],
			Business.BusinessName, 
			Business.Web,
			Business.BusinessPhone,
			Business.CellPhone,
			Business.TollFreePhone,
			Business.Fax,
			Business.StreetAddress1,
			Business.StreetAddress2, 
			Business.IsActive,
			Business.Deleted, 
			Business.City,
			Business.StateCode,
			Business.ZipCode,
			Business.[Description], 
			Business.CreditCardNo,
			Business.CreditCardExpDate,
			Business.CreditCardName,
			Business.CreditCardPin, 
			Business.BillingAddress1,
			Business.BillingAddress2,
			Business.BillingCity,
			Business.BillingStateCode,
			Business.BillingZipCode,
			Business.BillingUse, 
			Business.Latitude,
			Business.Longitude, 
			CONVERT(VARBINARY(MAX), NULL) AS Picture,
			Business.PictureContentType, 
			Business.ReferencedBy,
			Business.DateAdded,
			Open24_7 = dbo.fnIsBusinessOpened24_7(Business.BusinessId),
			isnull(vwLike.Likes, 0) as Likes,
			isnull(vwLike.Dislikes, 0) as Dislikes,
			vwBusinessRating.Rating,
			vwBusinessRating.CountReviews AS ReviewsCount,
			Business.Gmt
		FROM Business LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
					LEFT JOIN (SELECT BusinessId,
									SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
									SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
									CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
								FROM BusinessLike
								GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId			
					LEFT JOIN BlockBusiness AS bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
					LEFT JOIN UserFavorites AS uf ON uf.BusinessId = Business.BusinessId AND uf.UserId = @UserId
					LEFT JOIN (SELECT Promotion.BusinessId, MAX(PromotionCheckin.DateCheckin) AS LastCheckinDate 
								FROM PromotionCheckin LEFT JOIN Promotion ON PromotionCheckin.PromotionId = Promotion.PromotionId
								WHERE PromotionCheckin.UserId = @UserId
								GROUP BY Promotion.BusinessId) AS vwCheckin ON Business.BusinessId = vwCheckin.BusinessId											
		WHERE (Business.IsActive = 1)
			AND (bb.UserId IS NULL)	
			and (Business.City = @city)
			and
			(
				@searchString = '""'
				or contains(Business.*, @searchString)
			)
	end
END
go